package cn.changsheng.csfenjie.utils;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.bukkit.configuration.file.FileConfiguration;

/**
 * @title JDBC工具类 连接数据库 执行SQL 查询对象 查询集合
 * @author 长生道长
 */
public class JdbcUtil {

	/** 驱动名称 */
	private static final String DRIVER_NAME = "com.mysql.jdbc.Driver";

	/** 定义连接 */
	private static java.sql.Connection conn;
	/** 定义STMT */
	private static java.sql.PreparedStatement stmt;
	/** 定义结果集 */
	private static ResultSet rs;

	/** IP */
	private static String sqlIp = null;

	/** 端口 */
	private static String sqlPort = null;

	/** 数据库名 */
	private static String sqlName = null;

	/** 表名 */
	private static String sqlTable = null;

	/** 数据库用户名 */
	private static String sqlUser = null;

	/** 数据库密码 */
	private static String sqlPassword = null;
	private static ResultSet resultSet;

	/**
	 * 加载数据库
	 * 
	 * @return
	 * 
	 */
	/**
	 * 加载数据库
	 * 
	 * @return
	 * 
	 */
	public static void loadMysql() {
		try {
			YmlUtil ymlUtil = new YmlUtil();
			File file = ymlUtil.getFile("sql.yml");
			FileConfiguration fileMine = ymlUtil.getFileMine(file);

			sqlIp = fileMine.getString("sql.ip");
			sqlPort = fileMine.getString("sql.port");
			sqlName = fileMine.getString("sql.sqlName");
			sqlTable = fileMine.getString("sql.table");
			sqlUser = fileMine.getString("sql.user");
			sqlPassword = fileMine.getString("sql.password");
			final String url = "jdbc:mysql://" + sqlIp + ":" + sqlPort + "/" + sqlName
					+ "?useUnicode=true&characterEncoding=utf-8&useSSL=false";
			final String user = sqlUser;
			final String password = sqlPassword;

			Class.forName(DRIVER_NAME);
			conn = DriverManager.getConnection(url, user, password);
			// isDb(sqlTable);
		} catch (ClassNotFoundException e) {
			JdbcUtil.close();
			System.out.println("驱动加载失败");
			e.printStackTrace();
		} catch (SQLException e) {
			JdbcUtil.close();
			System.out.println("数据库链接异常");
			e.printStackTrace();

		}
	}

	/** 获取链接 */
	public static java.sql.Connection getConn() {
		return conn;
	}

	/** 关闭链接,释放资源 */
	public static void close() {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}

			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			System.err.println("资源释放发生异常");
		}
	}

	/**
	 * 创建表
	 * 
	 * @param dbNm 要創建的表名
	 * @return
	 */
	public static boolean isDb() {
		Statement st = null;
		try {
			java.sql.PreparedStatement state = conn.prepareStatement("create table if not exists " + sqlTable
					+ "(id int(4) primary key not null auto_increment,playerName TEXT,points TEXT,money TEXT,date TEXT)");
			state.executeUpdate();
			state = conn.prepareStatement("alter table " + sqlTable + " convert to character set utf8");
			state.executeUpdate();
			state.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			close();
		}
		return false;
	}

	/**
	 * 获取指定数据库下所有的表名
	 * 
	 * @param dbNm
	 * @return
	 */
	public static List<String> getAllTableName(String tableName) {
		List<String> result = new ArrayList<String>();
		Statement st = null;
		try {
			st = conn.createStatement();
			ResultSet rs = st.executeQuery(
					"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA='" + tableName + "'");
			while (rs.next()) {
				result.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			close();
		}
		return result;
	}

	/**
	 * 增加、删除、改
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static boolean updateByPreparedStatement(String sql, List<Object> params) throws SQLException {
		boolean flag = false;
		int result = -1;
		stmt = conn.prepareStatement(sql);
		int index = 1;
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				stmt.setObject(index++, params.get(i));
			}
		}
		result = stmt.executeUpdate();
		flag = result > 0 ? true : false;
		return flag;
	}

	/**
	 * 查询单条记录
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static Map<String, Object> findSimpleResult(String sql, List<Object> params) throws SQLException {
		Map<String, Object> map = new HashMap<String, Object>();
		int index = 1;
		stmt = conn.prepareStatement(sql);
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				stmt.setObject(index++, params.get(i));
			}
		}
		resultSet = stmt.executeQuery();// 返回查询结果
		ResultSetMetaData metaData = resultSet.getMetaData();
		int col_len = metaData.getColumnCount();
		while (resultSet.next()) {
			for (int i = 0; i < col_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				map.put(cols_name, cols_value);
			}
		}
		return map;
	}

	/**
	 * 查询多条记录
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static List<Map<String, Object>> findModeResult(String sql, List<Object> params) throws SQLException {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		int index = 1;
		stmt = conn.prepareStatement(sql);
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				stmt.setObject(index++, params.get(i));
			}
		}
		resultSet = stmt.executeQuery();
		ResultSetMetaData metaData = resultSet.getMetaData();
		int cols_len = metaData.getColumnCount();
		while (resultSet.next()) {
			Map<String, Object> map = new HashMap<String, Object>();
			for (int i = 0; i < cols_len; i++) {
				String cols_name = metaData.getColumnName(i + 1);
				Object cols_value = resultSet.getObject(cols_name);
				if (cols_value == null) {
					cols_value = "";
				}
				map.put(cols_name, cols_value);
			}
			list.add(map);
		}

		return list;
	}

	@SuppressWarnings("static-access")
	public static void main(String[] args) throws SQLException {

		JdbcUtil jdbcUtil = new JdbcUtil();
		// 刪除Name为？的记录
		String deleteSql = "DELETE FROM cons WHERE Name = ?";
		// 更新Name 为？的 name 和 UnlockLine
		String updateectSql = "UPDATE deaths SET zongzuie = ? WHERE playerName = ?";

		String insertSql = "insert into cons (playerName, price, money, date) values (?, ?, ?, ?)";
		// where id=1
		String secletSql = "select * from pluginList ";
		List<Object> params = new ArrayList<Object>();
		// params.add("istab");

		try {

			Map<String, Object> findSimpleResult = JdbcUtil.findSimpleResult(secletSql, params);
			System.out.println(findSimpleResult);
			// boolean flag = jdbcUtil.updateByPreparedStatement(secletSql, params);
			// System.out.println(flag);
		} catch (SQLException e) { // TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}
